package PCLiveServer

import (
	"github.com/ziutek/mymysql/mysql"
	_ "github.com/ziutek/mymysql/native" // Native engine
	// _ "github.com/ziutek/mymysql/thrsafe" // Thread safe engine
	"fmt"
	"time"
)

type OnlineStruct struct {
	UserName    string
	MAC         string
	HostName    string
	StartTime   time.Time
	CurrentTime time.Time
}

func GetDb() (mysql.Conn, error) {
	db := mysql.New("tcp", "", "127.0.0.1:3306", "PCLive", "", "PCLive")

	err := db.Connect()
	return db, err
}

func UserExist(username string) bool {
	db, err := GetDb()
	if err != nil {
		LogFile.Println("UserExist 连接失败-" + username)
		LogFile.Println(err)
		return false
	}
	SQL := "select username from UserTable where username='" + username + "'"
	rows, _, err := db.Query(SQL)
	if err != nil {
		LogFile.Println("UserExist 查询失败-" + username)
		LogFile.Println(err)
		return false
	}

	if len(rows) != 0 {
		return true
	}
	db.Close()
	return false
}

func GetUser(username string) (string, bool) {
	db, err := GetDb()
	if err != nil {
		LogFile.Println("UserExist 连接失败-" + username)
		LogFile.Println(err)
		return "", false
	}
	SQL := "select password from UserTable where username='" + username + "'"
	rows, res, err := db.Query(SQL)
	if err != nil {
		LogFile.Println("UserExist 查询失败-" + username)
		LogFile.Println(err)
		return "", false
	}

	if len(rows) != 0 {
		password := res.Map("password")
		strpassword := rows[0].Str(password)
		return strpassword, true
	}
	db.Close()
	return "", false
}

func GetOnlineWithMac(mac string) ([]OnlineStruct, bool) {
	var re []OnlineStruct

	db, err := GetDb()
	if err != nil {
		LogFile.Println("GetOnlineWithMac 连接失败-" + mac)
		LogFile.Println(err)
		return nil, false
	}

	SQL := "select * from OnlineTable where mac='" + mac + "'"
	rows, res, err := db.Query(SQL)
	if err != nil {
		LogFile.Println("GetOnlineWithMac 查询失败-" + mac)
		LogFile.Println(err)
		return re, false
	}

	if len(rows) == 0 {
		return re, false
	}

	for _, row := range rows {
		//  获取属性对应的索引
		usernamemap := res.Map("username")
		macmap := res.Map("mac")
		hostnamemap := res.Map("hostname")
		startTimemap := res.Map("starttime")
		endTimemap := res.Map("currenttime")

		var tmp OnlineStruct
		tmp.UserName = row.Str(usernamemap)
		tmp.MAC = row.Str(macmap)
		tmp.HostName = row.Str(hostnamemap)
		timetmp := row.Str(startTimemap)
		tmp.StartTime, _ = time.Parse("2006-01-02 15:04:05", timetmp)
		timetmp = row.Str(endTimemap)
		if len(timetmp) > 0 {
			tmp.CurrentTime, _ = time.Parse("2006-01-02 15:04:05", timetmp)
		}
		re = append(re, tmp)
	}
	db.Close()

	return re, true
}

func GetOnlineWithUser(user string) ([]OnlineStruct, bool) {
	var re []OnlineStruct

	db, err := GetDb()
	if err != nil {
		LogFile.Println("GetOnlineWithUser 连接失败-" + user)
		LogFile.Println(err)
		return nil, false
	}

	SQL := "select * from OnlineTable where username='" + user + "'"
	rows, res, err := db.Query(SQL)
	if err != nil {
		LogFile.Println("GetOnlineWithUser 查询失败-" + user)
		LogFile.Println(err)
		return re, false
	}

	if len(rows) == 0 {
		return re, false
	}

	for _, row := range rows {
		//  获取属性对应的索引
		usernamemap := res.Map("username")
		macmap := res.Map("mac")
		hostnamemap := res.Map("hostname")
		startTimemap := res.Map("starttime")
		endTimemap := res.Map("currenttime")

		var tmp OnlineStruct
		tmp.UserName = row.Str(usernamemap)
		tmp.MAC = row.Str(macmap)
		tmp.HostName = row.Str(hostnamemap)
		timetmp := row.Str(startTimemap)
		tmp.StartTime, _ = time.Parse("2006-01-02 15:04:05", timetmp)
		timetmp = row.Str(endTimemap)
		if len(timetmp) > 0 {
			tmp.CurrentTime, _ = time.Parse("2006-01-02 15:04:05", timetmp)
		}
		re = append(re, tmp)
	}
	db.Close()

	return re, true
}

func IsOnlineWithMac(mac string) (OnlineStruct, bool) {
	var tmptime OnlineStruct

	db, err := GetDb()
	if err != nil {
		LogFile.Println("IsOnlineWithMac 连接失败-" + mac)
		LogFile.Println(err)
		return tmptime, false
	}

	SQL := "select * from OnlineTable where mac='" + mac + "'"
	rows, res, err := db.Query(SQL)
	if err != nil {
		LogFile.Println("IsOnlineWithMac 查询失败-" + mac)
		LogFile.Println(err)
		return tmptime, false
	}

	if len(rows) == 0 {
		return tmptime, false
	}

	//  获取属性对应的索引
	startTimemap := res.Map("starttime")
	endTimemap := res.Map("currenttime")

	timestring := rows[0].Str(startTimemap)
	tmptime.StartTime, _ = time.Parse("2006-01-02 15:04:05", timestring)
	timestring = rows[0].Str(endTimemap)
	if len(timestring) > 0 {
		tmptime.CurrentTime, _ = time.Parse("2006-01-02 15:04:05", timestring)
	}

	db.Close()

	return tmptime, true
}

func InsertOnline(inseronline OnlineStruct) bool {
	db, err := GetDb()
	if err != nil {
		LogFile.Println("InsertOnline 连接失败")
		LogFile.Println(err)
		return false
	}

	stmt, err1 := db.Prepare("insert OnlineTable values(?, ?, ?, ?, ?)")
	if err1 == nil {
		_, err1 := stmt.Run(inseronline.UserName, inseronline.MAC, inseronline.HostName, inseronline.StartTime.Format("2006-01-02 15:04:05"), inseronline.CurrentTime.Format("2006-01-02 15:04:05"))
		if err1 != nil {
			LogFile.Println("InsertOnline 数据库插入失败")
			LogFile.Println(err)
		}
	} else {
		LogFile.Println("InsertOnline 数据库插入失败")
		LogFile.Println(err)
	}
	db.Close()
	return true
}

func DeleteOnline(mac string) bool {
	db, err := GetDb()
	if err != nil {
		LogFile.Println("DeleteOnline 连接失败- " + mac)
		LogFile.Println(err)
		return false
	}
	// 更新数据
	stmt, err1 := db.Prepare("delete from OnlineTable where mac=?")
	if err1 == nil {
		_, err1 := stmt.Run(mac)
		if err1 != nil {
			LogFile.Println("DeleteOnline 删除数据库失败- " + mac)
			LogFile.Println(err)
		}
	} else {
		LogFile.Println("DeleteOnline 删除数据库失败- " + mac)
		LogFile.Println(err)
	}
	db.Close()
	return true
}

func UpdateOnline(mac string, currenttime string) bool {
	db, err := GetDb()
	if err != nil {
		LogFile.Println("UpdateOnline 连接失败-" + mac)
		LogFile.Println(err)
		return false
	}
	// 更新数据
	stmt, err1 := db.Prepare("UPDATE OnlineTable SET currenttime=? WHERE mac=?")
	if err1 == nil {
		_, err1 := stmt.Run(currenttime, mac)
		if err1 != nil {
			LogFile.Println("UpdateOnline 更新失败-" + mac)
			LogFile.Println(err)
		}
	} else {
		LogFile.Println("UpdateOnline 更新失败-" + mac)
		LogFile.Println(err)
	}
	db.Close()
	return true
}

func InsertData(InserData OnlineStruct) bool {
	db, err := GetDb()
	if err != nil {
		LogFile.Println("InsertData 连接失败")
		LogFile.Println(err)
		return false
	}
	stmt, err1 := db.Prepare("insert " + InserData.UserName + "_DataTable" + " values(?, ?, ?, ?)")
	if err1 == nil {
		_, err1 := stmt.Run(InserData.MAC, InserData.HostName, InserData.StartTime.Format("2006-01-02 15:04:05"), InserData.CurrentTime.Format("2006-01-02 15:04:05"))
		if err1 != nil {
			LogFile.Println("InsertData 数据库插入失败")
			LogFile.Println(err)
		}
	} else {
		LogFile.Println("InsertData 数据库插入失败")
		LogFile.Println(err)
	}
	db.Close()
	return true
}

func GetDataWithMonth(tablename string, mac string, year string, month string) ([]OnlineStruct, bool) {
	var re []OnlineStruct

	db, err := GetDb()
	if err != nil {
		LogFile.Println("GetDataWithMonth 连接失败-" + mac)
		LogFile.Println(err)
		return nil, false
	}

	// "select * from AlwaysKing_DataTable where ((Year(starttime) = 2017 and Month(starttime) = 6) or (Year(shutdowntime) = 2017 and Month(shutdowntime) = 6)) and mac = ?"
	// ((Year(starttime) = ? and Month(starttime) = ?) or (Year(shutdowntime) = ? and Month(shutdowntime) = ?)) and
	SQL := "select * from " + tablename + " where ((Year(starttime) = ? and Month(starttime) = ?) or (Year(shutdowntime) = ? and Month(shutdowntime) = ?)) and mac = ?"
	stmt, err1 := db.Prepare(SQL)
	if err1 != nil {
		LogFile.Println("GetDataWithMonth 查询失败-" + mac)
		LogFile.Println(err)
		return re, false
	}

	res, err2 := stmt.Run(year, month, year, month, mac)
	if err2 != nil {
		LogFile.Println("GetDataWithMonth 查询失败-" + mac)
		LogFile.Println(err)
		return re, false
	}

	rows, _ := res.GetRows()
	if len(rows) == 0 {
		return re, false
	}

	for _, row := range rows {
		//  获取属性对应的索引
		macmap := res.Map("mac")
		hostnamemap := res.Map("hostname")
		startTimemap := res.Map("starttime")
		endTimemap := res.Map("shutdowntime")

		var tmp OnlineStruct
		tmp.MAC = row.Str(macmap)
		tmp.HostName = row.Str(hostnamemap)
		timetmp := row.Str(startTimemap)
		tmp.StartTime, _ = time.Parse("2006-01-02 15:04:05", timetmp)
		timetmp = row.Str(endTimemap)
		if len(timetmp) > 0 {
			tmp.CurrentTime, _ = time.Parse("2006-01-02 15:04:05", timetmp)
		}
		re = append(re, tmp)
	}
	db.Close()

	return re, true
}

func GetDataWithDay(tablename string, mac string, year string, month string, day string) ([]OnlineStruct, bool) {
	var re []OnlineStruct

	db, err := GetDb()
	if err != nil {
		LogFile.Println("GetDataWithMonth 连接失败-" + mac)
		LogFile.Println(err)
		return nil, false
	}

	// "select * from AlwaysKing_DataTable where ((Year(starttime) = 2017 and Month(starttime) = 6) or (Year(shutdowntime) = 2017 and Month(shutdowntime) = 6)) and mac = ?"
	// ((Year(starttime) = ? and Month(starttime) = ?) or (Year(shutdowntime) = ? and Month(shutdowntime) = ?)) and
	SQL := "select * from " + tablename + " where ((Year(starttime) = ? and Month(starttime) = ? and Day(starttime) = ?) or (Year(shutdowntime) = ? and Month(shutdowntime) = ? and Day(shutdowntime) = ?)) and mac = ?"
	stmt, err1 := db.Prepare(SQL)
	if err1 != nil {
		LogFile.Println("GetDataWithMonth 查询失败-" + mac)
		LogFile.Println(err)
		return re, false
	}

	res, err2 := stmt.Run(year, month, day, year, month, day, mac)
	if err2 != nil {
		LogFile.Println("GetDataWithMonth 查询失败-" + mac)
		LogFile.Println(err)
		return re, false
	}

	rows, _ := res.GetRows()
	if len(rows) == 0 {
		return re, false
	}

	for _, row := range rows {
		//  获取属性对应的索引
		macmap := res.Map("mac")
		hostnamemap := res.Map("hostname")
		startTimemap := res.Map("starttime")
		endTimemap := res.Map("shutdowntime")

		var tmp OnlineStruct
		tmp.MAC = row.Str(macmap)
		tmp.HostName = row.Str(hostnamemap)
		timetmp := row.Str(startTimemap)
		tmp.StartTime, _ = time.Parse("2006-01-02 15:04:05", timetmp)
		timetmp = row.Str(endTimemap)
		if len(timetmp) > 0 {
			tmp.CurrentTime, _ = time.Parse("2006-01-02 15:04:05", timetmp)
		}
		re = append(re, tmp)
	}
	db.Close()

	return re, true
}

// select *, count(distinct mac) from AlwaysKing_DataTable group by mac
func GetDataWithMachine(tablename string) ([]OnlineStruct, bool) {
	var re []OnlineStruct

	db, err := GetDb()
	if err != nil {
		LogFile.Println("GetDataWithMachine 连接失败-" + tablename)
		LogFile.Println(err)
		return nil, false
	}

	SQL := "select *, count(distinct mac) from " + tablename + " group by mac"
	fmt.Println(SQL)
	rows, res, err := db.Query(SQL)

	if len(rows) == 0 {
		return re, false
	}

	for _, row := range rows {
		//  获取属性对应的索引
		macmap := res.Map("mac")
		hostnamemap := res.Map("hostname")
		startTimemap := res.Map("starttime")
		endTimemap := res.Map("shutdowntime")

		var tmp OnlineStruct
		tmp.MAC = row.Str(macmap)
		tmp.HostName = row.Str(hostnamemap)
		timetmp := row.Str(startTimemap)
		tmp.StartTime, _ = time.Parse("2006-01-02 15:04:05", timetmp)
		timetmp = row.Str(endTimemap)
		if len(timetmp) > 0 {
			tmp.CurrentTime, _ = time.Parse("2006-01-02 15:04:05", timetmp)
		}
		re = append(re, tmp)
	}
	db.Close()

	return re, true
}
